package com.kashin.sistema;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * Esta clase maneja las conexiones a la base de datos y se encarga de todas las
 * operaciones con la misma:
 * <ul>
 * <li>viewTable(dbName, tableName)</li>
 * <li>executeQuery(query)</li>
 * <li>updateQuery(query)</li>
 * </ul>
 */
public class DBConnection {
    private Connection con = null;

    public DBConnection(String dbms, String serverName, String portNumber, String userName, String password) {
        try {
            con = this.getConnection(dbms, serverName, portNumber, userName, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection(String dbms, String serverName, String portNumber, String userName, String password)
            throws SQLException {
        Connection conn = null;
        Properties connectionProps = new Properties();
        connectionProps.put("user", userName);
        connectionProps.put("password", password);

        if (dbms.equals("mysql")) {
            conn = DriverManager.getConnection("jdbc:" + dbms + "://" + serverName + ":" + portNumber + "/",
                    connectionProps);
        }
        /*
         * TODO: compatibilidad para otras bases de datos, aunque no creo que lo
         * implemente.
         */
        System.out.println("Connected to database");
        return conn;
    }

    public Statement createStatement() throws SQLException {
        return con.createStatement();
    }

    public void viewTable(String dbName, String tableName) throws SQLException {
        Statement stmt = null;
        String query = "select * from " + dbName + "." + tableName;
        try {
            stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);

            ResultSetMetaData rsMetaData = rs.getMetaData();
            int numberOfColumns = rsMetaData.getColumnCount();

            while (rs.next()) {
                for (int i = 1; i < numberOfColumns; i++) {
                    System.out.print(rs.getString(i) + " ");
                }
                System.out.println(rs.getString(numberOfColumns));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            stmt.close();
        }
    }

    /**
     * Executes the given SQL statement, which returns a single ResultSet object
     * 
     * @param query
     *            an SQL statement to be sent to the database, typically a
     *            static SQL SELECT statement
     * @return a ResultSet object that contains the data produced by the given
     *         query; never null
     * @throws SQLException
     */
    public ResultSet executeQuery(String query) throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = con.createStatement();
            rs = stmt.executeQuery(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            stmt.close();
        }
        return rs;
    }

    /**
     * Executes the given SQL statement, which may be an INSERT, UPDATE, or
     * DELETE statement or an SQL statement that returns nothing
     * 
     * @param query
     *            INSERT, UPDATE or DELETE query
     * @throws SQLException
     * @return Either:
     *         <ol>
     *         <li>the row count for SQL Data Manipulation Language (DML)
     *         statements or</li>
     *         <li>0 for SQL statements that return nothing</li>
     *         </ol>
     */
    public int executeUpdate(String query) throws SQLException {
        Statement stmt = null;
        int ret = 0;
        try {
            stmt = con.createStatement();
            ret = stmt.executeUpdate(query);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            stmt.close();
        }
        return ret;
    }
/*
    public static void main(String[] args) {
        DBConnection mysqlCon = new DBConnection("mysql", "localhost", "3306", "root", "marine74327992");
        try {
            mysqlCon.viewTable("Matsushita", "Marcas");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    */
}
